﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Text;
using System.Data.OracleClient;
using Maticsoft.DBUtility;

namespace Xiaov.SmartWeb.Application.TZ.XB
{
  public partial class XBSJTZ : System.Web.UI.Page
  {
    ExcelHelper helpExcel = new ExcelHelper();
    protected void Page_Load(object sender, EventArgs e)
    {
      Response.Write("<div id='mydiv' >");
      Response.Write("_");
      Response.Write("</div>");
      Response.Write("<script>mydiv.innerText = '';</script>");
      Response.Write("<script language=javascript>;");
      Response.Write("var dots = 0;var dotmax = 10;function ShowWait()");
      Response.Write("{var output; output = '正在装载页面';dots++;if(dots>=dotmax)dots=1;");
      Response.Write("for(var x = 0;x < dots;x++){output += '·';}mydiv.innerText =  output;}");
      Response.Write("function StartShowWait(){mydiv.style.visibility = 'visible'; ");
      Response.Write("timeID =window.setInterval('ShowWait()',1000);}");
      Response.Write("function HideWait(){mydiv.style.visibility = 'hidden';");
      Response.Write("window.clearInterval(timeID );}");
      Response.Write("StartShowWait();</script>");
      Response.Flush();//这一句是最关键的

    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
      //System.Threading.Thread.Sleep(10000);
      //this.lbMessage.Text = "终于加载完成啦";
      //return;
      string startDate = txtStart.Text;
      string endDate = txtEnd.Text;
      if (!isDate(startDate) || !isDate(endDate))
      {
        lbMessage.Text = "日期格式不正确";
        return;
      }
      if (FileUpLoad1.HasFile)
      {
        string fileExtension = System.IO.Path.GetExtension(FileUpLoad1.FileName);
        if (fileExtension != ".xls" && fileExtension != ".xlsx")
        {
          lbMessage.Text = "请选择excel文件";
        }
        else
        {
          string path = Server.MapPath("~\\TZ\\XB\\File\\XBSJTZ") + fileExtension;
          FileUpLoad1.SaveAs(path);
          string conn = helpExcel.GetConn(path, fileExtension);
          string[] sheets = helpExcel.GetSheets(conn);
          for (int i = 0; i < sheets.Length; i++)
          {
            if (sheets[i] == "汇总表")
            { continue; }
            if (sheets[i] == "_xlnm#_FilterDatabase" || sheets[i] == "_xlnm#Print_Area" || sheets[i] == "_xlnm#Print_Titles")
            { continue; }
            DataTable dt = helpExcel.GetExcelTables(sheets[i], conn);
            if (dt.Rows.Count < 6)
            { continue; }
            //获取井队号与年份dt第一行第一列
            //string[] header = null;
            string jingdui = "";
            string year = "";
            string dw = "HG06";
            DataRow dr0 = dt.Rows[0];
            string f1 = dr0["F1"].ToString();
            //header = f1.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
            //if (header.Length != 3)
            //{
            //  Response.Write("sheet:" + sheets[i] + "第一个单元格格式不正确");
            //  return;
            //}
            //year = header[2].Length > 4 ? header[2].Substring(0, 4) : "";
            year = txtStart.Text.Substring(0, 4);
            if (year == "")
            {
              Response.Write("sheet:" + sheets[i] + "年度错误");
              return;
            }
            jingdui = sheets[i].Trim(" '$".ToCharArray());
            if (jingdui == "")
            {
              Response.Write("sheet:" + sheets[i] + "无井队号");
              return;
            }
            //break;
            //if (jingdui == "50791HB")
              ExceuteSheet(startDate, endDate, jingdui, dw, dt, year);
          }
          lbMessage.Text = "上传成功";
        }
      }
    }

    public void ExceuteSheet(string startDate, string endDate, string jingdui, string dw, DataTable dt, string year)
    {
      System.Text.StringBuilder sql = new System.Text.StringBuilder();
      //数据
      sql.Append("begin ");
      sql.Append(GetDelete(startDate, endDate, jingdui, dw));
      //bool isCurrentYear = false;//为去除开头几行去年数据, jinghaoJoin = ""//井号，为解决待井位问题
      string jinghao = "";
      System.Collections.Generic.List<string> jhPri = JHPri();
      for (int row = 5; row < dt.Rows.Count; row++)
      {
        DataRow dr = dt.Rows[row];
        if (string.IsNullOrEmpty(dr["F2"].ToString()))
        { continue; }
        if (string.IsNullOrEmpty(dr["F1"].ToString()))
        { continue; }
        string rq = year + "年" + dr["F1"].ToString();
        if (isDateTime(rq))
        {
          //if (jhPri.Contains(dr["F2"].ToString().Trim()))
          //{
          //  jinghaoJoin = jinghao + dr["F2"].ToString();
          //}
          //else
          //{
          //  jinghao = dr["F2"].ToString();
          //  jinghaoJoin = dr["F2"].ToString();
          //}
          //if (DateTime.Parse(rq).ToString("yyyyMMdd") == year + "0101")
          //{ isCurrentYear = true; }
          //if (!isCurrentYear)
          //{ continue; }
          DateTime dtNow = DateTime.Parse(rq);
          DateTime start = DateTime.Parse(startDate);
          DateTime end = DateTime.Parse(endDate);
          jinghao = dr["F2"].ToString();
          if (dtNow >= start && dtNow <= end)
          { sql.Append(GetInsert(dr, jingdui, dw, year, jinghao)); };
        }
      }
      sql.Append("null;end;");
      try
      {
        string s = sql.ToString();
        DbHelperOra.ExecuteSql(sql.ToString());
      }
      catch (Exception e)
      {
        Response.Write(jingdui + e.Message.ToString());
        //Response.End();
      }
      sql.Clear();
    }

    public System.Collections.Generic.List<string> JHPri()
    {
      System.Collections.Generic.List<string> jh = new System.Collections.Generic.List<string>();
      jh.Add("待井位");
      jh.Add("暂无井位");
      jh.Add("冬休");
      jh.Add("冬休准备");
      return jh;
    }

    public string GetDelete(string startDate, string endDate, string jingdui, string dw)
    {
      string s = "delete from TZ_XBZJSJ where rq>='{0}' and rq<='{1}' and dw='{3}' and jd='{2}';";
      return string.Format(s, startDate, endDate, jingdui.Trim(new char[] { '\'' }), dw);
    }

    public string GetInsert(DataRow dr, string jingdui, string dw, string year, string jinghao)
    {
      StringBuilder strSql = new StringBuilder();
      strSql.Append("insert into TZ_XBZJSJ(");
      strSql.Append(@"RQ,DW,JH,JD,LJJC,BRJC,JJZQSJ,ZJBAZBSJ,ZZTGSJ,BASJ,ZJGZZSJ,CZSJ,QXZSJ,JDGSJ,CJGZSJ,GJGZSJ,FZGZSJ,
ZZTGSSSJ,ZJSG,ZTSG,KZSG,GJSG,CJSG,JXSBSG,JPSKSG,QTSG,XLSJ,CLFZQKSJ,QTTGSJ,WJSJ,YZJSY,ZTCS,WJZX,SBJCDX,DJYJHTZ,ZRZH,
XQTGSJ,ZJRLSJ,SCJY,ZZ,ZJXH,SGQY,SGZT,ZJJB,ZJJX,ZJFL)");
      strSql.Append(" values (");
      strSql.Append(@"'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',
      '{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}',
      '{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}','{42}','{43}','{44}','{45}');");
      return string.Format(strSql.ToString(), DateTime.Parse(year + "年" + dr["F1"].ToString()).ToString("yyyy-MM-dd"), dw,
        dr["F2"].ToString().Trim(), jingdui.Trim(new char[] { '\'' }), stringTodouble(dr["F10"].ToString()),
        stringTodouble(dr["F11"].ToString()), TimeToMinutes(dr["F12"].ToString()), TimeToMinutes(dr["F13"].ToString()),
        TimeToMinutes(dr["F14"].ToString()), TimeToMinutes(dr["F15"].ToString()), TimeToMinutes(dr["F16"].ToString()),
       TimeToMinutes(dr["F17"].ToString()), TimeToMinutes(dr["F18"].ToString()), TimeToMinutes(dr["F19"].ToString()),
       TimeToMinutes(dr["F20"].ToString()), TimeToMinutes(dr["F21"].ToString()), TimeToMinutes(dr["F22"].ToString()),
       TimeToMinutes(dr["F23"].ToString()), TimeToMinutes(dr["F24"].ToString()), TimeToMinutes(dr["F25"].ToString()),
       TimeToMinutes(dr["F26"].ToString()), TimeToMinutes(dr["F27"].ToString()), TimeToMinutes(dr["F28"].ToString()),
        TimeToMinutes(dr["F29"].ToString()), TimeToMinutes(dr["F30"].ToString()), TimeToMinutes(dr["F31"].ToString()),
        TimeToMinutes(dr["F32"].ToString()), TimeToMinutes(dr["F33"].ToString()), TimeToMinutes(dr["F34"].ToString()),
        TimeToMinutes(dr["F35"].ToString()), TimeToMinutes(dr["F36"].ToString()), dr["F37"].ToString(),
        TimeToMinutes(dr["F38"].ToString()), TimeToMinutes(dr["F39"].ToString()), TimeToMinutes(dr["F40"].ToString()),
        TimeToMinutes(dr["F41"].ToString()), TimeToMinutes(dr["F42"].ToString()), TimeToMinutes(dr["F43"].ToString()),
         TimeToMinutes(dr["F44"].ToString()),
        dr["F3"].ToString().Trim(), dr["F4"].ToString().Trim(), dr["F5"].ToString().Trim(), dr["F6"].ToString().Trim(),
        dr["F7"].ToString().Trim(), dr["F8"].ToString().Trim(), dr["F9"].ToString().Trim());
    }
    public string stringTodouble(string str)
    {
      str = str.Trim();
      if (string.IsNullOrEmpty(str))
        return "";
      double i = 0;
      try
      {
        i = double.Parse(str);
      }
      catch
      {
        i = 0;
      }
      return i.ToString();
    }
    public string TimeToMinutes(string time)
    {
      time = time.Trim();
      string minutes = "";
      if (string.IsNullOrEmpty(time))
      {
        return "";
      }
      int index = time.IndexOf(':');
      if (index < 0)
      { return ""; }
      string hour = time.Substring(0, time.IndexOf(':'));
      string m = time.Substring(index + 1, time.Length - index - 1);
      TimeSpan ts;
      try
      {
        ts = new TimeSpan(int.Parse(hour), int.Parse(m), 0);
        minutes = ts.TotalMinutes.ToString();
      }
      catch
      {
        return "";
      }
      return minutes;
    }

    public bool isDate(string date)
    {
      if (string.IsNullOrEmpty(date))
        return false;
      if (date.Length != 10)
        return false;
      bool flag = true;
      try
      {
        DateTime dt = DateTime.Parse(date);
      }
      catch
      {
        flag = false;
      }
      return flag;
    }

    public bool isDateTime(string date)
    {
      bool flag = true;
      try
      {
        DateTime dt = DateTime.Parse(date);
      }
      catch
      {
        flag = false;
      }
      return flag;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
      lbMessage.Text = "aaa";
    }
  }
}